--    Author    : ChenErHao
--    Name      : DM.MID_T_MTH_END_M_AMONT_ONE.HQL
--    Functions : 利率-按月末存量计算_MID1
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-24  ChenErHao       1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE DM.MID_T_MTH_END_M_AMONT_ONE  PARTITION (DATA_MONTH = '#V_DATA_MONTH#') 
      select 
         BELONG_AREA_CODE, 
         SUM(DEP_BALANCE) DEP_BALANCE, 
         SUM(DEP_BALANCE_AMT) DEP_BALANCE_AMT, 
         SUM(LOAN_BALANCE) LOAN_BALANCE, 
         SUM(LOAN_BALANCE_AMT) LOAN_BALANCE_AMT, 
         SUM(DEP_BALANCE_L) DEP_BALANCE_L, 
         SUM(DEP_BALANCE_AMT_L) DEP_BALANCE_AMT_L, 
         SUM(LOAN_BALANCE_L) LOAN_BALANCE_L, 
         SUM(LOAN_BALANCE_AMT_L) LOAN_BALANCE_AMT_L
FROM (
        select 
        BELONG_AREA_CODE,
        DEP_BALANCE DEP_BALANCE,
        LOAN_OCCURRENCE_LEVEL DEP_BALANCE_AMT,
        0 LOAN_BALANCE,0 LOAN_BALANCE_AMT,0 DEP_BALANCE_L,0 DEP_BALANCE_AMT_L,0 LOAN_BALANCE_L,0 LOAN_BALANCE_AMT_L
    from EDW.DEPB A
  WHERE 
        DATA_DATE = '#V_DATA_DATE#'   
  UNION ALL
        select 
        BELONG_AREA_CODE,
        0 DEP_BALANCE,0 DEP_BALANCE_AMT,
        LOAN_BALANCE LOAN_BALANCE,
        LOAN_OCCURRENCE_LEVEL LOAN_BALANCE_AMT,
        0 DEP_BALANCE_L,0 DEP_BALANCE_AMT_L,0 LOAN_BALANCE_L,0 LOAN_BALANCE_AMT_L
    from EDW.LOAB A
  WHERE 
        DATA_DATE = '#V_DATA_DATE#'  
  UNION ALL
        select 
        AREA_NO_ID BELONG_AREA_CODE,
        0 DEP_BALANCE,0 DEP_BALANCE_AMT,0 LOAN_BALANCE,0 LOAN_BALANCE_AMT, 
        DEP_BALANCE DEP_BALANCE_L, 
        DEP_BALANCE_AMT DEP_BALANCE_AMT_L, 
        LOAN_BALANCE LOAN_BALANCE_L, 
        LOAN_BALANCE_AMT LOAN_BALANCE_AMT_L
    from DM.T_MTH_END_M_AMONT
  WHERE 
        FISCAL_MTH_DIMNSN_ID = '#V_LAST_1M_END_MONTH#'  and AREA_NO_ID!=-1)T 
  GROUP BY 
        BELONG_AREA_CODE
; 